package com.mrd.projects.weixinapi.dbutil;

import com.mrd.projects.weixinapi.entity.HongBaoTask;
import com.mrd.projects.weixinapi.entity.StateEnum;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * Created by luob on 2015/12/28 0028.
 */
public class HongBaoTaskDao {

    public void save(HongBaoTask task) throws Exception {
        Exception eresult = null;
        java.sql.Connection dbconn = ConnectDb.Connect();
        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();
        //执行SQL插入
        int n = 0;
        try {
            n = qRunner.update(
                    dbconn,
                    "insert into hongbaotask(" +
                            "template_id,user_account,reg_date,state,remark,bizids,amount,billno)" +
                            " values(?,?,datetime('now','localtime'),?,?,?,?,?)",
                    new Object[]{task.getTemplate_id(),
                            task.getUser_account(),
                            task.getState() == null ? StateEnum.TEMP : task.getState(),
                            task.getRemark(), task.getBizids(), task.getAmount(), task.getBillno()});
        } catch (SQLException e) {
            e.printStackTrace();
            eresult = e;
        }
        System.out.println("成功插入" + n + "条数据！");
        //关闭数据库连接
        DbUtils.closeQuietly(dbconn);
        if (eresult != null) {
            throw eresult;
        }
    }

    public void update(HongBaoTask task) throws Exception {
        Exception eresult = null; 
        Connection conn = ConnectDb.Connect();

        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();
        //执行SQL插入
        int n = 0;
        try {
            n = qRunner.update(conn, "update hongbaotask set state = ?," +
                            " remark=? where id=?",
                    new Object[]{task.getState(),
                            task.getRemark(),
                            task.getId()});
        } catch (SQLException e) {
            e.printStackTrace();
            eresult = e;
        }
        System.out.println("成功更新" + n + "条数据！");

        //关闭数据库连接
        DbUtils.closeQuietly(conn);
        if (eresult != null) {
            throw eresult;
        }
    }

    public void del(HongBaoTask task) throws Exception {
        Exception eresult = null; 
        Connection conn = ConnectDb.Connect();

        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();
        //执行SQL插入
        int n = 0;
        try {
            n = qRunner.update(conn, "delete from hongbaotask where id=?",
                    new Object[]{task.getId()});
        } catch (SQLException e) {
            e.printStackTrace();
            eresult = e;
        }
        System.out.println("成功删除" + n + "条数据！");

        //关闭数据库连接
        DbUtils.closeQuietly(conn);
        if (eresult != null) {
            throw eresult;
        }
    }

    public HongBaoTask findNextTask() throws Exception {
        Exception eresult = null;
        Connection conn = ConnectDb.Connect();
        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();
        @SuppressWarnings("unchecked")
        Object[] list = null;
        try {
            ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>() {
                public Object[] handle(ResultSet rs) throws SQLException {
                    Exception eresult = null; 
                    if (!rs.next()) {
                        return null;
                    }
                    ResultSetMetaData meta = rs.getMetaData();
                    int cols = meta.getColumnCount();
                    Object[] result = new Object[cols];
                    for (int i = 0; i < cols; i++) {
                        result[i] = rs.getObject(i + 1);
                    }
                    return result;
                }
            };
            list = qRunner.query(conn, "select id,template_id,user_account,billno,bizids from hongbaotask where state='TEMP' order by reg_date desc limit 1", h);
            HongBaoTask t = new HongBaoTask();
            if (list == null || list.length == 0) {
                t = null;
            } else {
                t.setId((Integer) list[0]);
                t.setTemplate_id((String) list[1]);
                t.setUser_account((String) list[2]);
                t.setBillno((String) list[3]);
                t.setBizids((String)list[4]);
            }
            return t;
        } catch (SQLException e) {
            e.printStackTrace();
            eresult = e;
        }
        //关闭数据库连接
        DbUtils.closeQuietly(conn);
        if (eresult != null) {
            throw eresult;
        }
        return null;

    }
}
